﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommunitySystemModel;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Data;

namespace CommunitySystem.BO.SystemManager
{

    public class DataDictionaryBo
    {
        //AccessHelper AH = new AccessHelper();

        #region 单例
        private static DataDictionaryBo instance = null;
        private string strConnection;
        private DataDictionaryBo()
        {
            strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["JinYang"].ConnectionString;
        }

        public static DataDictionaryBo GetInstance()
        {
            if (instance == null)
                instance = new DataDictionaryBo();
            return instance;
        }
        #endregion



        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="DataModel"></param>
        /// <param name="DDIM"></param>
        /// <returns></returns>
        public int SaveDataDictionary(DataDictionaryModel DataModel, ObservableCollection<DataDictionaryItemModel> DDIM)
        {
            //事务开始
            //SqlConnection conn = null;
            //SqlTransaction Sc = AccessHelper.GetInstance().BeginTransaction(out conn);
            string sql = "";
            int PID = 0;
            List<SqlParameter> list = null;
            if (DataModel.Id == 0)
            //插入主表
            {
//                sql = string.Format(@"insert into DataDictionary(DictName,DictCode,IsDynamic,DynamicSQL,CanDelete,IsSystem,IsAutoLoad,IsTree) values(
//                                           '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",
//                                                DataModel.DictName, DataModel.DictCode, DataModel.IsDynamic, DataModel.DynamicSQL, DataModel.CanDelete, DataModel.IsSystem,
//                                                DataModel.IsAutoLoad, DataModel.IsTree);
                //PID =  AccessHelper.GetInstance().SaveEntity(DataModel, "", null);

                sql = AccessHelper.GeneratorInsertSQL(DataModel, new string[1] { "Item" }, "Id", "DataDictionary", out list);
                //返回当前索引
                PID = int.Parse(AccessHelper.GetInstance().ExcuteDataSet(sql, list.ToArray()).Tables[0].Rows[0][0].ToString());

            }
            else
            {
                //sql = string.Format(@"update DataDictionary set DictName='{0}',DictCode='{1}',IsDynamic='{2}',DynamicSQL='{3}',CanDelete='{4}',IsSystem='{5}',IsAutoLoad='{6}',IsTree='{7}' where ID={8}", DataModel.DictName, DataModel.DictCode, DataModel.IsDynamic, DataModel.DynamicSQL, DataModel.CanDelete, DataModel.IsSystem,
                //                                DataModel.IsAutoLoad, DataModel.IsTree, DataModel.Id);

                sql = AccessHelper.GeneratorUpdateSQL(DataModel, new string[1] { "Item" }, "Id", "DataDictionary", out list);
                PID = DataModel.Id;

                AccessHelper.GetInstance().ExcuteDataSet(sql , list.ToArray());
            }
            
            

            //int PID = int.Parse(AccessHelper.GetInstance().ExcuteDataSet("select @@identity from DataDictionary").Tables[0].Rows[0][0].ToString());

            if (null != DDIM)
            {
                ///插入项
                foreach (var item in DDIM)
                {
                    //新增的才插入
                    if(item.Id==0)
                        AccessHelper.GetInstance().ExecuteNonQuery(string.Format(@"insert into DataDictionaryItem(DictCode,DictValue,DictPinYin,ParentId) values('{0}','{1}','{2}',{3})", item.DictCode, item.DictValue, item.DictPinYin, PID));
                    else
                        AccessHelper.GetInstance().ExecuteNonQuery(string.Format(@"UPDATE DataDictionaryItem
   SET [ParentId] = {3}
      ,[DictCode] = '{0}'
      ,[DictValue] = '{1}'
      ,[DictPinYin] = '{2}'
      ,[Sec] = {4} where ID = {5}", item.DictCode, item.DictValue, item.DictPinYin, PID,item.Sec,item.Id));
                }
            }

            //结束
            //AccessHelper.GetInstance().EndTransaction(Sc);

            return PID;
        }
       
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IList<DataDictionaryModel> GetDictionarys()
        {
            var listbysql = new List<DataDictionaryModel>();
            DataSet ds = new AccessHelper().ExcuteDataSet("select Id, DictName,DictCode,case IsDynamic when 'false' then '静态' when 'true' then '动态' end as Type from DataDictionary");
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                listbysql.Add(new DataDictionaryModel() { DictName = r["DictName"].ToString(), DictCode = r["DictCode"].ToString(), Id = Convert.ToInt32(r["Id"].ToString()),  DynamicSQL = r["Type"].ToString() });

            }
            return listbysql;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IList<DataDictionaryItemModel> GetDataDictionaryItem(int PID)
        {
            var listbysql = new List<DataDictionaryItemModel>();
            DataSet ds = new AccessHelper().ExcuteDataSet(string.Format("select * from DataDictionaryItem where ParentID={0} order by Sec",PID));
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                listbysql.Add(new DataDictionaryItemModel() { DictValue = r["DictValue"].ToString(), DictCode = r["DictCode"].ToString(), Id = Convert.ToInt32(r["Id"].ToString()), DictPinYin = r["DictPinYin"].ToString(), Sec = !string.IsNullOrEmpty(r["Sec"].ToString()) ? Convert.ToInt32(r["Sec"].ToString()) : 0 });

            }
            return listbysql;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IList<DataDictionaryModel> GetDataDictionary(int PID)
        {
            var listbysql = new List<DataDictionaryModel>();
            DataSet ds = new AccessHelper().ExcuteDataSet(string.Format("select * from DataDictionary where ID={0}", PID));
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                listbysql.Add(new DataDictionaryModel() { DictName = r["DictName"].ToString(), DictCode = r["DictCode"].ToString(), Id = Convert.ToInt32(r["Id"].ToString()), IsDynamic = Convert.ToBoolean(r["IsDynamic"]), IsTree = Convert.ToBoolean(r["IsTree"]), DynamicSQL = r["DynamicSQL"].ToString() });

            }
            return listbysql;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <returns></returns>
        public int DeleteDataDictionary(int PID)
        {
             //删除子集
             new AccessHelper().ExecuteNonQuery(string.Format(@"delete from DataDictionaryItem where ParentID={0}", PID));
            //删除父级并返回
             return new AccessHelper().ExecuteNonQuery(string.Format(@"delete from DataDictionary where ID={0}", PID));
        }


        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int DeleteDataDictionaryItem(int ID)
        {
            return new AccessHelper().ExecuteNonQuery(string.Format(@"delete from DataDictionaryItem where ID={0}", ID));
        }
    }
}
